package DBModule;

import java.io.IOException;
import java.sql.*;
import java.util.*;

import Test.*;
import Test.Exception.FactoryException;
import User.User;
import Tool.Tool;

public class TestPaperManager {
	private TestPaperManager(){}	// 不允许实例化
	
	private static String qbTableName = "QuestionBody";
	
	/**
	 * 得到所有满足下列条件的题面的编号
	 * @param course	科目
	 * @param type		题型
	 * @return			所有编号的集合
	 */
	private static ArrayList<Integer> getAllQIDs(final Course course, final int type) throws Exception{
		final ArrayList<Integer> ret = new ArrayList<Integer>();
		DBQuerier<Integer> querier = new DBQuerier<Integer>() {
			@Override
			public Collection<Integer> query(Connection conn, Object... keys)
					throws SQLException, IOException, FactoryException {
				PreparedStatement ps = null;
				ps = conn.prepareStatement("select QID from " + qbTableName + " where CID=? and type=?");
				ps.setInt(1, course.getId());
				ps.setInt(2, type);
				ResultSet rs = ps.executeQuery();
				while (rs.next())
					ret.add(rs.getInt(1));
				ps.close();
				return null;
			}
		};
		DBManager.query(querier);
		return ret;
	}
	
	private static void closePS(PreparedStatement... ps) throws SQLException{
		for (PreparedStatement p : ps)
			if (p != null)
				p.close();
	}
	
	/**
	 * 为用户生成一份随机试题
	 * @param user			用户
	 * @param course		所属科目
	 * @param types, counts 类别为types[i]的题型有counts[i]道
	 * @return				试题
	 */
	public static TestPaper genRandowTestPaper(User user, Course course, int[] types, int []counts) throws Exception{
		if (user == null || user.getType() != User.STUDENT) return null;
		
		TestPaper ret = new TestPaper();
		TestPaperState state = ret.getState();
		state.setCourse(course);	// 科目
		state.setStudentID(user.getID());	// 学生
		ArrayList<Integer> res = new ArrayList<Integer>();
		
		// 为每一种题型添加相应数量的题目
		for (int i = 0; i < types.length; ++i){
			ArrayList<Integer> numbers = getAllQIDs(course, types[i]);
			int count = counts[i];
			if (numbers.size() <= count) res.addAll(numbers);
			else {
				Collections.shuffle(numbers);
				while (--count >= 0) res.add(numbers.get(count));
			}
		}
				
		// 编号并加入到结果
		Iterator<Question> iter = QuestionTable.getQuestion(course, res.iterator()).iterator();
		for (int i = 1; iter.hasNext(); ++i){
			Question q = iter.next();
			q.getContent().setNumber(i);
			ret.pushQuestion(q);
		}
		return ret;
	}
	
	/**
	 * 得到某同学所有考卷的状态：包括他还没考的 
	 */
	public static Collection<TestPaperState> getAllState(final User user) throws Exception{
		if (user == null || user.getType() != User.STUDENT) throw new Exception("未登陆或用户类型不正确");
		DBQuerier<TestPaperState> querier = new DBQuerier<TestPaperState>() {
			public Collection<TestPaperState> query(Connection conn, Object... keys)
					throws SQLException, IOException, FactoryException {
				TreeMap<Course, TestPaperState> map = new TreeMap<Course, TestPaperState>();
				
				// 得到该学生已作答的试卷的状态信息，加到结果内
				PreparedStatement ps1 = conn.prepareStatement("select ID, CID, state, score from TestPaper where SID=?");
				ps1.setInt(1, user.getID());
				for (ResultSet rs = ps1.executeQuery(); rs.next();){
					TestPaperState state = new TestPaperState();
					state.setStudentID(user.getID());
					state.setId(rs.getInt(1));
					state.setCourse(CourseTable.getCourse(rs.getInt(2)));
					state.setState(rs.getInt(3));
					state.setScore(rs.getInt(4));
					map.put(state.getCourse(), state);
				}
				
				// 得到该学生已选的科目
				PreparedStatement ps2 = conn.prepareStatement("select CID from Student_Course where UID=?");
				ps2.setInt(1, user.getID());
				
				for (ResultSet rs = ps2.executeQuery(); rs.next();){
					Course course = CourseTable.getCourse(rs.getInt(1));
					if (map.containsKey(course)) continue;					
					TestPaperState state = new TestPaperState();
					state.setStudentID(user.getID());
					state.setCourse(course);
					state.setState(TestPaper.UNSOLVED);
					map.put(course, state);
				}
				
				closePS(ps1, ps2);
				return map.values();
			}
		};
		return DBManager.query(querier);
	}
	
	/**
	 * 保存试卷，同时修改其试卷号
	 * @param paper
	 * @throws Exception 
	 */
	public static void insertPaper(final TestPaper paper) throws Exception{
		if (paper == null) return;
		DBUpdater<TestPaper> saver = new DBUpdater<TestPaper>() {
			public void update(Connection conn, TestPaper... opt) throws SQLException,
					IOException {
				// 基本信息
				TestPaperState state = paper.getState();
				Collection<Question> questions = paper.getQuestions();
				PreparedStatement ps1 = conn.prepareStatement("insert into TestPaper(CID, state, SID, score) values(?,?,?,?)");
				ps1.setInt(1, state.getCourse().getId());
				ps1.setInt(2, state.getState());
				ps1.setInt(3, state.getStudentID());
				ps1.setInt(4, state.getScore());
				ps1.executeUpdate();
				
				// 更新序号
				PreparedStatement ps2 = conn.prepareStatement("select max(ID) from TestPaper where CID=? and SID=?");
				ps2.setInt(1, state.getCourse().getId());
				ps2.setInt(2, state.getStudentID());
				ResultSet rs = ps2.executeQuery();
				if (!rs.next()) throw new SQLException("插入数据库错误");
				state.setId(rs.getInt(1));

				// 其他条目
				PreparedStatement ps3 = conn.prepareStatement("insert into Content(num, PID, QID, TID, score, data) values(?,?,?,?,?,?)");
				Iterator<Question> iter = questions.iterator();
				while(iter.hasNext()){
					Question q = iter.next();
					QuestionBody body = q.getBody();
					Content cont = q.getContent();
					ps3.setInt(1, cont.getNumber());
					ps3.setInt(2, state.getId());
					ps3.setInt(3, body.getId());
					ps3.setInt(4, 0);
					ps3.setInt(5, cont.getScore());
					ps3.setBinaryStream(6, Tool.getInputStream(cont));
					ps3.executeUpdate();
				}
				closePS(ps1, ps2, ps3);
			}
		};
		
		DBManager.update(saver, paper);
	}
	
	/**
	 * 保存试卷，不修改其试卷号
	 * @param paper
	 * @throws Exception 
	 */
	public static void savePaper(final TestPaper paper) throws Exception{
		if (paper == null) return;
		DBUpdater<TestPaper> saver = new DBUpdater<TestPaper>() {
			public void update(Connection conn, TestPaper... opt) throws SQLException,
					IOException {
				// 基本信息
				TestPaperState state = paper.getState();
				Collection<Question> questions = paper.getQuestions();
				PreparedStatement ps1 = conn.prepareStatement("update TestPaper set CID=?, state=?, SID=?, score=? where ID=?");
				ps1.setInt(1, state.getCourse().getId());
				ps1.setInt(2, state.getState());
				ps1.setInt(3, state.getStudentID());
				ps1.setInt(4, state.getScore());
				ps1.setInt(5, state.getId());
				ps1.executeUpdate();

				// 其他条目
				PreparedStatement ps2 = conn.prepareStatement(
						"update Content set QID=?, TID=?, score=?, data=? where num=? and PID=?");
				Iterator<Question> iter = questions.iterator();
				while(iter.hasNext()){
					Question q = iter.next();
					QuestionBody body = q.getBody();
					Content cont = q.getContent();
					ps2.setInt(1, body.getId());
					ps2.setInt(2, cont.getTeacherID());
					ps2.setInt(3, cont.getScore());
					ps2.setBinaryStream(4, Tool.getInputStream(cont));
					ps2.setInt(5, cont.getNumber());
					ps2.setInt(6, state.getId());
					ps2.executeUpdate();
				}
				closePS(ps1, ps2);
			}
		};
		
		DBManager.update(saver, paper);
	}
	

	/**
	 * 读取试卷
	 * @param paperID	试卷号
	 * @return
	 * @throws FactoryException 
	 * @throws IOException 
	 * @throws SQLException 
	 */
	public static TestPaper loadPaper(final int paperID) throws SQLException, IOException, FactoryException{
		final TestPaper ret = new TestPaper();
		DBQuerier<TestPaper> querier = new DBQuerier<TestPaper>() {
			public Collection<TestPaper> query(Connection conn, Object... keys)
					throws SQLException, IOException, FactoryException {				
				// 取基本信息
				PreparedStatement ps1 = conn.prepareStatement("select CID, state, SID, score from TestPaper where ID=?");
				ps1.setInt(1, paperID);
				ResultSet rs1 = ps1.executeQuery();
				if (!rs1.next()) throw new SQLException("无此试卷号：" + paperID);
				TestPaperState state = ret.getState();
				state.setId(paperID);
				state.setCourse(CourseTable.getCourse(rs1.getInt(1)));
				state.setState(rs1.getInt(2));
				state.setStudentID(rs1.getInt(3));
				state.setScore(rs1.getInt(4));
				
				
				
				// 取具体题目
				PreparedStatement ps2 = conn.prepareStatement("select QID from Content where PID=? order by num");
				ps2.setInt(1, ret.getState().getId());
				
				ResultSet rs2 = ps2.executeQuery();
				ArrayList<Integer> qids = new ArrayList<Integer>();
				while (rs2.next())
					qids.add(rs2.getInt(1));
				
				Collection<Question> qs = QuestionTable.getQuestion(state.getCourse(), qids.iterator());
				Iterator<Question> iter = qs.iterator();
				
				PreparedStatement ps3 = conn.prepareStatement("select TID, score, data from Content where PID=? order by num");
				ps3.setInt(1, paperID);
				ResultSet rs3 = ps3.executeQuery();
				while (rs3.next()){
					Question q = iter.next();
					Content cont = q.getContent();
					cont.setTeacherID(rs3.getInt(1));
					cont.setScore(rs3.getInt(2));
					cont.fromBytes(rs3.getBinaryStream(3));
					ret.pushQuestion(q);
				}
				
				closePS(ps1, ps2, ps3);
				return null;
			}
		};
		DBManager.query(querier);
		return ret;
	}
	
	/**
	 * 得到某一教师所负责的全部科目
	 * @param teacher
	 * @return 科目集合
	 * @throws Exception	错误信息 
	 */
	public static Collection<Course> getAllCourses(final User teacher) throws Exception{
		if (teacher == null || teacher.getType() != User.TEACHER)
			throw new Exception("用户未登陆或不是教师");
		DBQuerier<Course> querier = new DBQuerier<Course>() {
			public Collection<Course> query(Connection conn, Object... keys)
					throws SQLException, IOException, FactoryException {
				Collection<Course> ret = new ArrayList<Course>();
				PreparedStatement ps = conn.prepareStatement("select CID from Teacher_Course where UID=?");
				ps.setInt(1, teacher.getID());
				ResultSet rs = ps.executeQuery();
				while (rs.next())
					ret.add(CourseTable.getCourse(rs.getInt(1)));
				
				return ret;
			}
		};
		return DBManager.query(querier);
	}
	
	/**
	 * 得到某一科目所有试卷情况
	 * @param course	试卷所属科目
	 * @param flag		为真则只返回未批阅完的试卷，否则返回所有试卷 
	 */
	public static Collection<TestPaperState> getAllTestPaperBrief(final Course course, final boolean flag) throws Exception{
		DBQuerier<TestPaperState> querier = new DBQuerier<TestPaperState>() {
			public Collection<TestPaperState> query(Connection conn, Object... keys)
					throws SQLException, IOException, FactoryException {
				String str;
				Collection<TestPaperState> ret = new ArrayList<TestPaperState>();
				if (flag) str = "select ID, state, SID, score from TestPaper where CID=? and state<>" + TestPaper.CORRECTED;
				else str = "select ID, state, SID, score from TestPaper where CID=?";
				
				PreparedStatement ps1 = conn.prepareStatement(str);
				ps1.setInt(1, course.getId());
				ResultSet rs = ps1.executeQuery();
				while (rs.next()){
					TestPaperState state = new TestPaperState();
					state.setCourse(course);
					state.setId(rs.getInt(1));
					state.setState(rs.getInt(2));
					state.setStudentID(rs.getInt(3));
					state.setScore(rs.getInt(4));
					ret.add(state);
				}
				
				closePS(ps1);
				return ret;
			}
		};
		return DBManager.query(querier);
	}
}











